package com.tjetc.dao.impl;

import com.tjetc.dao.FeeDao;
import com.tjetc.domain.Fee;
import com.tjetc.domain.Medreport;
import com.tjetc.utils.Db;
import com.tjetc.utils.PageInfo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class FeeDaoImpl implements FeeDao {
    @Override
    public List<Fee> listNoDeal() throws SQLException {
        return Db.runner0().query("select * from fee where status = 1",new BeanListHandler<>(Fee.class));
    }


    @Override
    public void add(Fee fee) throws SQLException {
        Db.runner().update(Db.getConnection(),"insert into fee(rid, type, account) VALUES (?,?,?)",
                fee.getRid(),fee.getType(),fee.getAccount());
    }

    @Override
    public Fee listMyFee(int rid) throws SQLException {
        return Db.runner0().query("select * from fee where rid = ?",new BeanHandler<>(Fee.class),rid);
    }

    @Override
    public void updateStatus(int id) throws SQLException {
        Db.runner0().update("update fee set status = 1 where id = ?",id);
    }

    @Override
    public void finishFee(int id) throws SQLException {
        Db.runner().update(Db.getConnection(),"update fee set status = 2 where id = ?",id);
    }

    @Override
    public int getMaxId() throws SQLException {
        return Db.runner0().query("select max(id) from fee",new ScalarHandler<>());
    }

    @Override
    public void rollBackStatus(int id) throws SQLException {
        Db.runner0().update("update fee set status = 0 where id = ?",id);
    }
    @Override
    public List<Medreport> listByDate(String date) throws SQLException {
        return Db.runner0().query("SELECT c.name AS name, SUM(m.price * otc.num) AS value\n" +
                "FROM record r\n" +
                "JOIN otc ON r.id = otc.rid\n" +
                "JOIN medicine m ON otc.mid = m.id\n" +
                "JOIN category c ON m.cid = c.id\n" +
                "JOIN fee f ON r.id = f.rid\n" +
                "WHERE DATE(r.date) = ? AND f.status = 2\n" +
                "GROUP BY c.name",new BeanListHandler<>(Medreport.class),date);
    }

    @Override
    public List<Fee> list(PageInfo<Fee> pageInfo) throws SQLException {
        return Db.runner0().query("select * from fee order by status asc limit ?,? ",
                new BeanListHandler<>(Fee.class),
                pageInfo.getStartRecord(),pageInfo.getPageSize());
    }

    @Override
    public int getCount() throws SQLException {
        return ((Long)Db.runner0().query("select count(id) from fee",
                new ScalarHandler<>())).intValue();
    }
}
